//
//  SqliteManager.swift
//  Mircore
//
//  Created by apple on 16/8/21.
//  Copyright © 2016年 飞羽. All rights reserved.
//

import UIKit


//数据库名称

let  DBName = "SinaWeibo.db"

//数据库存储的沙盒路径

let  DBPath  = (NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(DBName)

//数据库操作专用类型

class SqliteManager: NSObject {
    
    //单例全局访问点
    static  let  sharedManager: SqliteManager = SqliteManager()
    
    //数据库操作队列
    
    lazy  var  queue  = FMDatabaseQueue(path: DBPath)
    
    
    //构造函数私有化
    
    private  override init() {
        
        super.init()
        
        createTables()
    }
    
    
    //通过查询sql语句返回数组字典
    
    func  queryDicArrayForSql(sql: String)  ->  [[String: AnyObject]] {
        
        var  tempArray  = [[String: AnyObject]]()
        
        queue.inDatabase { (db) in
            
            let  resultSet = db.executeQuery(sql, withArgumentsInArray: nil)
            
            //遍历结果集  判断是否有下一条记录
            
            while resultSet.next()  {
                
                //获取列数
                
                let  cols  = resultSet.columnCount()
                
                var dic  = [String: AnyObject]()
                
                //遍历列数
                
                for  i  in  0..<cols {
                    
                    //获取列名
                    
                    let  colName = resultSet.columnNameForIndex(i)
                    
                    //获取列名对应的值
                    
                    let  colValue  =  resultSet.objectForColumnIndex(i)
                    
                    //添加键值对
                    
                    dic[colName]  = colValue
                    
                }
                
                tempArray.append(dic)
            }
        }
        
        return  tempArray
        
    }
    
    
    //查询结果以字典的形式输出
    
    func  selectIntoDic() {
        
        //准备sql
        
        let  sql = "SELECT * FROM T_PERSON"
        
        //执行sql
        
        queue.inDatabase { (db) in
            
            let  resultSet  = db.executeQuery(sql, withArgumentsInArray: nil)
            
            //遍历结果集  判断是否有下一条记录
            
            while  resultSet.next()  {
                
                //获取列数
                
                let  cols = resultSet.columnCount()
                
                var  dic = [String: AnyObject]()
                
                //遍历列数
                
                for  i in  0..<cols {
                    
                    //获取列名
                    
                    let  colName = resultSet.columnNameForIndex(i)
                    
                    //获取列名对应的值
                    
                    let  colValue  = resultSet.objectForColumnIndex(i)
                    
                    //添加键值对
                    
                    dic[colName]  = colValue
                    
                }
                
            }
        }
        
    }
    
    
    //删除数据
    
    func  delete() {
        
        //准备sql语句
        
        let sql  = "DELETE FROM  T_PERSON  WHERE  ID = ?"
        
        //执行sql
        
        queue.inDatabase { (db) in
            
            let  result = db.executeUpdate(sql, withArgumentsInArray: [2])
            
            if result {
                
                print("删除成功")
            }else  {
                
                print("删除失败")
            }
            
        }
        
    }
    
    //修改数据
    
    func  update() {
        
        //准备sql
        
        let  sql = "UPDATE T_PERSON  SET  NAME = ? WHERE ID = ?"
        
        //执行sql
        
        queue.inDatabase { (db) in
            
            let  result  = db.executeUpdate(sql, withArgumentsInArray: ["杨幂", 2])
            
            if result {
                
                print("修改成功")
            }else  {
                
                print("修改失败")
            }
        }
        
    }
    
    //查询数据
    
    func  select()  {
        
        //准备sql
        
        let  sql  = "SELECT  ID, NAME, AGE FROM T_FERSON"
        
        //执行sql语句
        
        queue.inDatabase { (db) in
            
            //查询到的结果集
            
            let  resultSet  = db.executeQuery(sql, withArgumentsInArray: nil)
            
            //判断其是否是下一条记录
            
            while  resultSet.next()  {
                
                //获取id
                
                let  id  = resultSet.intForColumn("ID")
                
                //获取名字
                
                let  name  = resultSet.stringForColumn("NAME")
                
                //年龄
                
                let  age = resultSet.stringForColumn("AGE")
                
                print("id: \(id), name: \(name), age: \(age)")
                
            }
        }
    }
    
    
    //插入数据
    
    func  insert() {
        
        //准备sql
        
        let  sql  = "INSERT INTO  T_PERSON (NAME, AGE) VALUES (?, ?)"
        
        //执行sql语句
        
        queue.inDatabase { (db) in
            
            let  result  = db.executeUpdate(sql, withArgumentsInArray: ["杨钰莹", 18])
            
            if result {
                
                print("插入成功")
            }else {
                
                print("插入失败")
            }
        }
    }
    
    
    //创建表
    
    private  func    createTables() {
        
        
        let  path  = NSBundle.mainBundle().pathForResource("db.sql", ofType: nil)!
        
        let  sql  = try!  String(contentsOfFile: path)
        
        //可以执行增加删除修改查询
        
        queue.inDatabase { (db) in
            
            let  result = db.executeStatements(sql)
            
            if  result {
                
                print("创表成功")
            }else {
                
                print("创表失败")
                
            }
        }
    }
    
    
    
    
}
